Concatenates two or more strings into one. Non-string arguments must be explicitly converted to string using TO_STRING() function

CONCAT(TO_STRING(float_attr), ',', TO_STRING(int_attr), ',', title)

LEVENSHTEIN ( pattern, source, {normalize=0, length_delta=0}) returns number (Levenshtein distance) of single-character edits (insertions, deletions or substitutions) between pattern and source strings required to make in pattern to make it source.

pattern, source - constant string, string field name, JSON field name or any expression which produces a string (like e.g. SUBSTRING_INDEX())
normalize - option to get distance as a float number in the range of [0.0 - 1.0], where 0.0 is the exact match and 1.0 is the maximum difference. Default value is 0, means to not normalize and provide the result as integer.
length_delta - to skip Levenshtein distance calculation and return max(strlen(pattern), strlen(source)) in case the option is set and lengths of the strings differ more than by length_delta value. Default value is 0, means to calculate Levenshtein distance for any input strings. This option could be useful in case of checking mostly similar strings.

SELECT LEVENSHTEIN('gily', attr1) AS dist, WEIGHT() AS w FROM test WHERE MATCH('test') ORDER BY w DESC, dist ASC;
SELECT LEVENSHTEIN('gily', j.name, {length_delta=6}) AS dist, WEIGHT() AS w FROM test WHERE MATCH('test') ORDER BY w DESC;
SELECT LEVENSHTEIN(title, j.name, {normalize=1}) AS dist, WEIGHT() AS w FROM test WHERE MATCH ('test') ORDER BY w DESC, dist ASC;

REGEX(attr,expr) function returns 1 if regular expression matched to string of attribute and 0 otherwise. It works with both string and JSON attributes.

SELECT REGEX(content, 'box?') FROM test;
SELECT REGEX(j.color, 'red | pink') FROM test;

The expression should follow the RE2 syntax. For example, for case insensitive search you can do:

SELECT REGEX(content, '(?i)box') FROM test;

SNIPPET() can be used to highlight search results in a given text. The first two arguments are: the text to highlight, and a query. It's possible to pass options to the function as the third, fourth and so on arguments. SNIPPET() can fetch the text to use in highlighting from the table itself. The first argument in this case should be field name:

SELECT SNIPPET(body, QUERY()) FROM myIndex WHERE MATCH('my.query')

QUERY() expression in this example returns current fulltext query. SNIPPET() can also highlight non-indexed text:

mysql  SELECT id, SNIPPET('text to highlight', 'my.query', 'limit=100') FROM myIndex WHERE MATCH('my.query')

It can also be used to highlight the text fetched from other sources using an UDF:

SELECT id, SNIPPET(myUdf(id), 'my.query', 'limit=100') FROM myIndex WHERE MATCH('my.query')

where myUdf() would be a UDF that fetches a document by its ID from some external storage. SNIPPET() is a "post limit" function, meaning that computing snippets is postponed not just until the entire final result set is ready, but even after the LIMIT clause is applied. For example, with a LIMIT 20,10 clause, SNIPPET() will be called at most 10 times.

Note, SNIPPET() doesn't support limiting by fields. Use HIGHLIGHT() instead.

SUBSTRING_INDEX(string, delimiter, number) returns a substring of a string before a specified number of delimiter occurs

string - The original string. Can be a constant string or a string from a string/json attribute.
delimiter - The delimiter to search for
number - The number of times to search for the delimiter. Can be both a positive or negative number.If it is a positive number, this function will return all to the left of the delimiter. If it is a negative number, this function will return all to the right of the delimiter.

SELECT SUBSTRING_INDEX('www.w3schools.com', '.', 2) FROM test;
SELECT SUBSTRING_INDEX(j.coord, ' ', 1) FROM test;

Other functions

Returns ids of documents inserted or replaced by last statement in the current session.

The same value can be also received via @@session.last_insert_id variable:

mysql> select @@session.last_insert_id;
+--------------------------+
| @@session.last_insert_id |
+--------------------------+
| 11,32                    |
+--------------------------+
1 rows in set

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 25,26,29         |
+------------------+
1 rows in set

SELECT @@system_variable [LIMIT [offset,] row_count]

This is currently a placeholder query that does nothing and reports success. That is in order to keep compatibility with frameworks and connectors that automatically execute this statement.

However @@session.last_insert_id and LAST_INSERT_ID() report ID of documents these were inserted or replaced well at last statement.

mysql> select @@session.last_insert_id;
+--------------------------+
| @@session.last_insert_id |
+--------------------------+
| 11,32                    |
+--------------------------+
1 rows in set

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 25,26,29         |
+------------------+
1 rows in set

Returns current connection id.

mysql> select CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 6               |
+-----------------+
1 row in set (0.00 sec)

String functions Backup and restore

▪️ Securing and compacting a table

It's crucial to regularly back up your tables to recover them in case of system crashes, hardware failure, or data corruption/loss. Backups are also necessary before upgrading to a new version of Manticore Search that changes the table format, and for transferring data to another system when migrating to a new server.

The manticore-backup tool, included in the official Manticore Search packages, automates the process of backing up tables for an instance running in RT mode.

If you followed the official installation instructions, you should already have everything installed and don't need to worry. Otherwise, manticore-backup requires PHP 8.1.10 and specific modules or manticore-executor, which is a part of the manticore-extra package, and you need to ensure that one of these is available.

Note that manticore-backup is not available for Windows yet.

First, make sure you're running manticore-backup on the same server where the Manticore instance you are about to back up is running.

Second, we recommend running the tool under the root user so the tool can transfer ownership of the files you are backing up. Otherwise, a backup will be also made but with no ownership transfer. In either case, you should make sure that manticore-backup has access to the data dir of the Manticore instance.

The only required argument for manticore-backup is --backup-dir, which specifies the destination for the backup. If you don't provide any additional arguments, manticore-backup will:

locate a Manticore instance running with the default configuration
create a subdirectory in the --backup-dir directory with a timestamped name
backup all tables found in the instance

‹›

Example

Example

📋

manticore-backup --config=path/to/manticore.conf --backup-dir=backupdir

‹›

Response

Copyright (c) 2023, Manticore Software LTD (https://manticoresearch.com)

Manticore config file: /etc/manticoresearch/manticore.conf
Tables to backup: all tables
Target dir: /mnt/backup/

Manticore config
  endpoint =  127.0.0.1:9308

Manticore versions:
  manticore: 5.0.2
  columnar: 1.15.4
  secondary: 1.15.4
2022-10-04 17:18:39 [Info] Starting the backup...
2022-10-04 17:18:39 [Info] Backing up config files...
2022-10-04 17:18:39 [Info]   config files - OK
2022-10-04 17:18:39 [Info] Backing up tables...
2022-10-04 17:18:39 [Info]   pq (percolate) [425B]...
2022-10-04 17:18:39 [Info]    OK
2022-10-04 17:18:39 [Info]   products (rt) [512B]...
2022-10-04 17:18:39 [Info]    OK
2022-10-04 17:18:39 [Info] Running sync
2022-10-04 17:18:42 [Info]  OK
2022-10-04 17:18:42 [Info] You can find backup here: /mnt/backup/backup-20221004171839
2022-10-04 17:18:42 [Info] Elapsed time: 2.76s
2022-10-04 17:18:42 [Info] Done

To back up specific tables only, use the --tables flag followed by a comma-separated list of tables, for example --tables=tbl1,tbl2. This will only backup the specified tables and ignore the rest.

‹›

Example

Example

📋

manticore-backup --backup-dir=/mnt/backup/ --tables=products

‹›

Response

Copyright (c) 2023, Manticore Software LTD (https://manticoresearch.com)

Manticore config file: /etc/manticoresearch/manticore.conf
Tables to backup: products
Target dir: /mnt/backup/

Manticore config
  endpoint =  127.0.0.1:9308

Manticore versions:
  manticore: 5.0.3
  columnar: 1.16.1
  secondary: 0.0.0
2022-10-04 17:25:02 [Info] Starting the backup...
2022-10-04 17:25:02 [Info] Backing up config files...
2022-10-04 17:25:02 [Info]   config files - OK
2022-10-04 17:25:02 [Info] Backing up tables...
2022-10-04 17:25:02 [Info]   products (rt) [512B]...
2022-10-04 17:25:02 [Info]    OK
2022-10-04 17:25:02 [Info] Running sync
2022-10-04 17:25:06 [Info]  OK
2022-10-04 17:25:06 [Info] You can find backup here: /mnt/backup/backup-20221004172502
2022-10-04 17:25:06 [Info] Elapsed time: 4.82s
2022-10-04 17:25:06 [Info] Done

Argument	Description
`--backup-dir=path`	This is the path to the backup directory where the backup will be stored. The directory must already exist. This argument is required and has no default value. On each backup run, manticore-backup will create a subdirectory in the provided directory with a timestamp in the name (`backup-[datetime]`), and will copy all required tables to it. So the `--backup-dir` is a container for all your backups, and it's safe to run the script multiple times.
`--restore[=backup]`	Restore from `--backup-dir`. Just --restore lists available backups. `--restore=backup` will restore from `<--backup-dir>/backup`.
`--config=/path/to/manticore.conf`	Path to Manticore config. This is optional. If it's not passed, a default one for your operating system will be used. It's used to get the host and port to communicate with the Manticore daemon.
`--tables=tbl1,tbl2, ...`	Semicolon-separated list of tables that you want to back up. To back up all tables, omit this argument. All the provided tables must exist in the Manticore instance you are backing up from, or the backup will fail.
`--compress`	Whether the backed up files should be compressed. Not enabled by default.
`--unlock`	In rare cases when something goes wrong, tables can be left in a locked state. Use this argument to unlock them.
`--version`	Show the current version.
`--help`	Show this help.

You can also back up your data through SQL by running the simple command BACKUP TO /path/to/backup.

Note, this command is not supported in Windows yet.

BACKUP
  [{TABLE | TABLES} a[, b]]
  [{OPTION | OPTIONS}
    async = {on | off | 1 | 0 | true | false | yes | no}
    [, compress = {on | off | 1 | 0 | true | false | yes | no}]
  ]
  TO path_to_backup

For instance, to back up tables a and b to the `/backup directory, run the following command:

BACKUP TABLES a, b TO /backup

There are options available to control and adjust the backup process, such as:

async: makes the backup non-blocking, allowing you to receive a response with the query ID immediately and run other queries while the backup is ongoing. The default value is 0.
compress: enables file compression using zstd. The default value is 0. For example, to run a backup of all tables in async mode with compression enabled to the /tmp directory:

BACKUP OPTION async = yes, compress = yes TO /tmp

The path should not contain special symbols or spaces, as they are not supported.
Ensure that Manticore Buddy is launched (it is by default).

To restore a Manticore instance from a backup, use the manticore-backup command with the --backup-dir and --restore arguments. For example: manticore-backup --backup-dir=/path/to/backups --restore. If you don't provide any argument for --restore, it will simply list all the backups in the --backup-dir.

‹›

Example

Example

📋

manticore-backup --backup-dir=/mnt/backup/ --restore

‹›

Response

Copyright (c) 2023, Manticore Software LTD (https://manticoresearch.com)

Manticore config file:
Backup dir: /tmp/

Available backups: 3
  backup-20221006144635 (Oct 06 2022 14:46:35)
  backup-20221006145233 (Oct 06 2022 14:52:33)
  backup-20221007104044 (Oct 07 2022 10:40:44)

To start a restore job, run manticore-backup with the flag --restore=backup name, where backup name is the name of the backup directory within the --backup-dir. Note that:

There can't be any Manticore instance running on the same host and port as the one being restored.
The old manticore.json file must not exist.
The old configuration file must not exist.
The old data directory must exist and be empty.

If all conditions are met, the restore will proceed. The tool will provide hints, so you don't have to memorize them. It's crucial to avoid overwriting existing files, so make sure to remove them prior to the restore if they still exist. Hence all the conditions.

‹›

Example

Example

📋

manticore-backup --backup-dir=/mnt/backup/ --restore=backup-20221007104044

‹›

Response

Copyright (c) 2023, Manticore Software LTD (https://manticoresearch.com)

Manticore config file:
Backup dir: /tmp/
2022-10-07 11:17:25 [Info] Starting to restore...

Manticore config
  endpoint =  127.0.0.1:9308
2022-10-07 11:17:25 [Info] Restoring config files...
2022-10-07 11:17:25 [Info]   config files - OK
2022-10-07 11:17:25 [Info] Restoring state files...
2022-10-07 11:17:25 [Info]   config files - OK
2022-10-07 11:17:25 [Info] Restoring data files...
2022-10-07 11:17:25 [Info]   config files - OK
2022-10-07 11:17:25 [Info] The backup '/tmp/backup-20221007104044' was successfully restored.
2022-10-07 11:17:25 [Info] Elapsed time: 0.02s
2022-10-07 11:17:25 [Info] Done

Other functions Few words about RT table structure

String functions

CONCAT()

LEVENSHTEIN()

REGEX()

SNIPPET()

SUBSTRING_INDEX()

Other functions

LAST_INSERT_ID()

SELECT @@system_variable

CONNECTION_ID()

▪️ Securing and compacting a table

Backup and restore

Installation

How to use

Arguments

BACKUP SQL command reference

General syntax of BACKUP

Important considerations

Restore